package calbs.socket.dao;/**
 * Created by zhangqin on 2017/8/8.
 */

import calbs.socket.constant.ServerConstant;
import calbs.socket.exception.AnalySocketException;
import calbs.socket.exception.AnalySocketExceptionEmum;
import calbs.socket.pojo.req.ReqUploadAbLineMsg;
import calbs.socket.pojo.req.ReqUploadLocationMsg;
import calbs.socket.pojo.req.ReqUploadWarningMsg;
import calbs.socket.pojo.resp.RespAbLineListMsg;
import calbs.socket.pojo.resp.RespAbLineMsg;
import calbs.socket.pojo.resp.RespSoftUpdateVersionMsg;
import calbs.socket.pojo.resp.RespUploadAbLineMsg;
import calbs.socket.spring.SpringUtil;
import calbs.socket.tool.BeanMapTool;
import calbs.socket.tool.LatLngTool;
import calbs.socket.tool.MapTool;
import calbs.socket.tool.UUIDTool;
import calbs.socket.tool.date.DateStyle;
import calbs.socket.tool.date.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * ab先Dao层
 *
 * @author 张钦
 * @create 2017-08-08 15:47
 **/
@Service
public class ServerDao {

    private JdbcTemplate jdbcTemplate = SpringUtil.getBean(JdbcTemplate.class);
    /**
     * 查询AB线
     */
    private static final String ABLINE_LIST_SQL = "select" +
            " a_lat aLat,a_lng aLng ,a_height aHeight,b_lat bLat,b_lng bLng,b_height bHeight,id abLineId,name abLineName ,basex,basey,basez,farmland_sign fieldName,UNIX_TIMESTAMP(create_time) utcTime" +
            " from wn_ab_line" +
            " where" +
            " (a_lng+b_lng)/2 >= ? and (a_lng+b_lng)/2 <= ? and (a_lat+b_lat)/2 >= ? and  (a_lat+b_lat)/2 <= ? ";
    private static final String DEVICE_SELECT_COUNT = "select count(id) c from wn_machine_device where device_number = ?";
    private static final String DEVICE_SELECT_ID = "select id,farm_id from wn_machine_device where device_number = ?";
    private static final String NEW_VERSION = "select version_code newVersionCode,file_address softAddress,UNIX_TIMESTAMP(create_time) utcTime from app_version order by version_code desc limit 0,1";
    private static final String INSERT_AB_LINE = " insert into wn_ab_line(id,a_lat,a_lng,b_lat,a_height,b_lng,b_height,create_time,terminal_sign,name,farmland_sign,basex,basey,basez) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";

    private static final String QUERY_SEQUENCE = " select number from sys_sequence where belong_bo = ? ";
    private static final String UPDATE_SEQUENCE = " update sys_sequence set number = number+1 where belong_bo = ? ";
    private static final String INSERT_SEQUENCE = " insert into sys_sequence(belong_bo,number) values(?,?) ";
    private static final String QUERY_COUNT_LOCATION_NEW = "select count(device_id) c from wn_lnglat_automatic_new where device_id = ? ";
    private static final String INSERT_LOCATION_NEW = "INSERT INTO wn_lnglat_automatic_new(device_id,device_sign," +
            "create_time," +
            "lng," +
            "lat," +
            "height," +
            "speed," +
            "orentation," +
            "ab_line_id," +
            "work_width," +
            "control_type," +
            "machines_tool, state)" +
            " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String UPDATE_LOCATION_NEW = "update  wn_lnglat_automatic_new " +
            "set create_time = ?," +
            " lng = ?," +
            " lat = ?," +
            " height =? ," +
            " speed =?," +
            " orentation =?," +
            " ab_line_id =?," +
            " work_width =?," +
            " control_type =?," +
            " machines_tool =?, state =? " +
            " where device_id = ?";
    private static final String INSERT_LOCATION = "INSERT INTO wn_lnglat_automatic(device_id,device_sign," +
            "create_time," +
            "lng," +
            "lat," +
            "height," +
            "speed," +
            "orentation," +
            "ab_line_id," +
            "work_width," +
            "control_type," +
            "machines_tool,state)" +
            " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";

    private static final String INSERT_wn_machine_device_active = " insert into wn_machine_device_active(device_id,create_time,create_date,farm_id)values(?,?,?,?)";
    private static final String SELECT_wn_machine_device_active = " select count(id) c from wn_machine_device_active where device_id = ? and create_date = ? ";
    /*
    <列表标号><AB线ID><田块标识><AB线名称><A点经度><A点纬度><A点高程><B点经度><B点纬度><B点高程><UTC时间>*/

    private static final String INSERT_WARINGG = "insert into app_waring(id,device,warning_code,create_time,waring_utc_time) values (?,?,?,?,?)";
    public RespAbLineListMsg getRespAbLineList(double latitude,double longitude){
        RespAbLineListMsg respAbLineListMsg = new RespAbLineListMsg();
        double r = 6371;//地球半径千米
        double dis = 5;//5千米距离
        double dlng =  2*Math.asin(Math.sin(dis/(2*r))/Math.cos(latitude*Math.PI/180));
        dlng = dlng*180/Math.PI;//角度转为弧度
        double dlat = dis/r;
        dlat = dlat*180/Math.PI;
        double minlat =latitude-dlat;
        double maxlat = latitude+dlat;
        double minlng = longitude -dlng;

        double maxlng = longitude + dlng;
        Object[] params = {minlng,maxlng,minlat,maxlat};
        List<RespAbLineMsg> lineMsgList = new ArrayList<>();

        try {
            lineMsgList= BeanMapTool.mapsToObjects(jdbcTemplate.queryForList(ABLINE_LIST_SQL,params),RespAbLineMsg.class);
        } catch (Exception e) {
            throw  new AnalySocketException(AnalySocketExceptionEmum.SYS_ERROR);
        }
        List<RespAbLineMsg> lineMsgListDis = new ArrayList<>();
        if(lineMsgList!=null){
            for (RespAbLineMsg respAbLineMsg : lineMsgList) {
                double lat = (Double.parseDouble(respAbLineMsg.getaLat())+Double.parseDouble(respAbLineMsg.getbLat()))/2;
                double lng = (Double.parseDouble(respAbLineMsg.getaLng())+Double.parseDouble(respAbLineMsg.getbLng()))/2;
                respAbLineMsg.setDistance(MapTool.getdistance(lat,lng,latitude,longitude));
            }
            Collections.sort(lineMsgList);
            Set<RespAbLineMsg> respAbLineMsgSet = new HashSet<>();
            respAbLineMsgSet.addAll(lineMsgList);

            lineMsgListDis.addAll(respAbLineMsgSet);
            for (int i = 0; i < lineMsgListDis.size(); i++) {
                RespAbLineMsg respAbLineMsg = lineMsgListDis.get(i);
                respAbLineMsg.setSerialNumber(i+1);
            }
        }
        if(lineMsgListDis.size()>10){
            lineMsgListDis = lineMsgListDis.subList(0,10);
        }
        respAbLineListMsg.setLineMsgs(lineMsgListDis);
        return respAbLineListMsg;

    }

    public boolean hasDevice (String imie){
        Object[] params = {imie};
        Long aLong = null;
        try{
            aLong = jdbcTemplate.queryForObject(DEVICE_SELECT_COUNT, params, Long.class)
            ;
        }catch (Exception e){
            aLong = null;
        }
        if(aLong!=null){
            return aLong>0;
        }
        return false;
    }


    public RespSoftUpdateVersionMsg getNewVersion(){
        RespSoftUpdateVersionMsg respSoftUpdateVersionMsg = new RespSoftUpdateVersionMsg();
        respSoftUpdateVersionMsg  = BeanMapTool.mapToBean(jdbcTemplate.queryForMap(NEW_VERSION),respSoftUpdateVersionMsg);

        return respSoftUpdateVersionMsg;
    }


    @Transactional
    public RespUploadAbLineMsg uploadAbLineMsg(ReqUploadAbLineMsg abLineMsg){
        //(id,a_lat,a_lng,b_lat,a_height,b_lng,b_height,create_time,terminal_sign,utc_time,name,farmland_sign)
        int abLineId =getSequence(ServerConstant.ABLINE_SEQUENCE);
        Date date = new Date();
        Object[] params = {abLineId,abLineMsg.getaPointLatitude(),abLineMsg.getaPointLongitude(),abLineMsg.getbPointLatitude(),abLineMsg.getaPointHeight(),abLineMsg.getbPointLongitude(),abLineMsg.getbPointHeight()
                ,DateUtil.DateToString(date, DateStyle.YYYY_MM_DD_HH_MM_SS),abLineMsg.getDevice(),abLineMsg.getAbLineName(),abLineMsg.getFieldName(),abLineMsg.getBasex(),abLineMsg.getBasey(),abLineMsg.getBasez()};
        jdbcTemplate.update(INSERT_AB_LINE,params);
        RespUploadAbLineMsg respUploadAbLineMsg = new RespUploadAbLineMsg();
        respUploadAbLineMsg.setAdLineID(abLineId);
        respUploadAbLineMsg.setFieldName(abLineMsg.getFieldName());
        return respUploadAbLineMsg;
    }

    @Transactional
    public void insertLocation(ReqUploadLocationMsg reqUploadLocationMsg){

        Object[] paramsMachine = {reqUploadLocationMsg.getDevice()};
        String farmId = null;
        String deviceId = null;
        try{
            Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(DEVICE_SELECT_ID, paramsMachine);
            deviceId= String.valueOf(stringObjectMap.get("id"));
            farmId = String.valueOf(stringObjectMap.get("farm_id"))
            ;
        }catch (Exception e){
            deviceId = "-1";
        }
        Object[] params = {deviceId,reqUploadLocationMsg.getDevice(),
                DateUtil.DateToString(new Date(new BigDecimal(reqUploadLocationMsg.getUtcTime()).multiply(new BigDecimal(1000)).longValue()), DateStyle.YYYY_MM_DD_HH_MM_SS),
                reqUploadLocationMsg.getLongitude(),
                reqUploadLocationMsg.getLatitude(),
                reqUploadLocationMsg.getHeight(),
                reqUploadLocationMsg.getSpeed(),
                reqUploadLocationMsg.getDirection(),
                reqUploadLocationMsg.getAbLineID(),
                reqUploadLocationMsg.getWidth(),
                reqUploadLocationMsg.getControlType(),
                reqUploadLocationMsg.getAgMachineTool(),reqUploadLocationMsg.getState()};
        jdbcTemplate.update(INSERT_LOCATION,params);
        //添加更新信息
        Long  newLatlngCount = 0l;
        try{
            newLatlngCount = jdbcTemplate.queryForObject(QUERY_COUNT_LOCATION_NEW, new Object[]{deviceId}, Long.class)
            ;
        }catch (Exception e){
            newLatlngCount = 0l;
        }
        if(newLatlngCount>0){
            Object[] updateParam = {
                    DateUtil.DateToString(new Date(new BigDecimal(reqUploadLocationMsg.getUtcTime()).multiply(new BigDecimal(1000)).longValue()), DateStyle.YYYY_MM_DD_HH_MM_SS),
                    reqUploadLocationMsg.getLongitude(),
                    reqUploadLocationMsg.getLatitude(),
                    reqUploadLocationMsg.getHeight(),
                    reqUploadLocationMsg.getSpeed(),
                    reqUploadLocationMsg.getDirection(),
                    reqUploadLocationMsg.getAbLineID(),
                    reqUploadLocationMsg.getWidth(),
                    reqUploadLocationMsg.getControlType(),
                    reqUploadLocationMsg.getAgMachineTool(),reqUploadLocationMsg.getState(),deviceId};
            jdbcTemplate.update(UPDATE_LOCATION_NEW,updateParam);
        }else{
            jdbcTemplate.update(INSERT_LOCATION_NEW,params);
        }
        //添加活动设备信息
        Long  deviceCount = 0l;
        try{
            Object[] paramsMachineQuery = {deviceId,DateUtil.DateToString(new Date(),DateStyle.YYYY_MM_DD)};
            deviceCount = jdbcTemplate.queryForObject(SELECT_wn_machine_device_active, paramsMachineQuery, Long.class)
            ;
        }catch (Exception e){
            deviceCount = 0l;
        }
        if(deviceCount>0){
        }else{
            Object[] paramsMachineInsert = {deviceId,DateUtil.DateToString(new Date(),DateStyle.YYYY_MM_DD_HH_MM_SS),DateUtil.DateToString(new Date(),DateStyle.YYYY_MM_DD),farmId};
            jdbcTemplate.update(INSERT_wn_machine_device_active,paramsMachineInsert);
        }

    }

    public void insertWarning(ReqUploadWarningMsg reqUploadWarningMsg){
//id,device,warning_code,create_time,waring_utc_time
        Object[] params = {UUIDTool.getUUID(),reqUploadWarningMsg.getDevice(),reqUploadWarningMsg.getWarningCode(),DateUtil.DateToString(new Date(),DateStyle.YYYY_MM_DD_HH_MM_SS)
        ,Integer.parseInt(reqUploadWarningMsg.getUtcTime())};
        jdbcTemplate.update(INSERT_WARINGG,params);
    }
    private int getSequence(String key){
        Object[] params = {key};
        Long aLong = null;
        try{
              aLong = jdbcTemplate.queryForObject(QUERY_SEQUENCE, params, Long.class);
        }catch (Exception e){
            aLong = null;
        }

        if(aLong==null){
            int startNumber = 1;
            Object[] paramsInsert = {key,startNumber};
            jdbcTemplate.update(INSERT_SEQUENCE,paramsInsert);
            return  startNumber;
        }else{
             jdbcTemplate.update(UPDATE_SEQUENCE, params);
          return jdbcTemplate.queryForObject(QUERY_SEQUENCE, params, Long.class).intValue();
        }
    }

    public static void main(String[] args) throws ParseException {

    }
}
